import pymysql
from pymysql.cursors import Cursor
from pymysql.converters import escape_string

host = "localhost"
user = "park"
dbname = "park"
password = "CKS6NARsNDRpEbTD"


class Database:
    def __init__(self):
        self.conn = None
        self.cursor: Cursor = None

    def __enter__(self):
        try:
            self.conn = self.connect()
            self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
            return self
        except Exception as e:
            raise e

    def __exit__(self, exc_type, exc_value, traceback):
        self.close()

    def execute(self, sql, args=None):
        if not args:
            args = None
        self.cursor.execute(sql, args)

    def connect(self):
        return pymysql.connect(host=host, user=user, password=password, database=dbname)

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()

    def get_insert_sql_from_obj(self, obj, table_name):

        sql = f"INSERT INTO {table_name} ("

        if isinstance(obj, dict):
            dt = obj
        else:
            dt = obj.__dict__

        if "id" in dt:
            dt.pop("id")

        for key in dt.keys():
            sql += f"{key},"

        sql = sql[0:-1] + ") VALUES ("

        for value in dt.values():
            if isinstance(value, str):
                sql += f'"{escape_string(value)}",'
            else:
                sql += f'"{value}",'

        sql = sql[0:-1] + ")"

        return sql

    def get_update_sql_from_obj(self, id, obj, table_name):

        sql = f"UPDATE {table_name} SET "

        dt = obj.__dict__

        if "id" in dt:
            dt.pop("id")

        for k, v in dt.items():
            sql += f"`{k}` = "
            if isinstance(v, str):
                sql += f'"{escape_string(v)}",'
            else:
                sql += f'"{v}",'

        sql = sql.strip(",")

        sql += f' WHERE `id` = "{id}"'

        return sql
